Relational Database Design and Modeling for Software Engineers
Rate this course
Ask Author
Contribute
Back to course home

0% completed

Vote For New Content
Introduction
Course Overview
Before the Advent of Database
Fundamental Concepts
What is a Database?
Database management systems (DBMS)
Overview of Relational Databases
Relational Vs. Non-Relational Databases
Data Modeling
Introduction to Data Modeling
Levels of Data Modeling
Data Modeling Process
Best Practices in Data Modeling
Quiz
Entity-Relationship (ER) Data Modeling
Introduction to ER Models
Entities, Attributes, and Relationships
Cardinality and Participation
Attributes of Relationship Types
Weak Entity Types
Creating an ER Diagram for Employee Management System
Quiz
The Relational Data Model
Introduction to the Relational Model
Relations, Tuples, and Attributes
Keys in Relational Databases
Relational Integrity Constraints
Converting ER Diagrams to Relational Model
Best Practices for ER Diagrams to Relational Models
Quiz
Functional Dependency
Introduction to Functional Dependency
Types of Functional Dependencies
Inference Rules for Functional Dependencies
Closure of Attribute Sets
Quiz
Normalization
Introduction to Normalization
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Higher Normal Forms (4NF, 5NF)
Exercise 1
Solution to Exercise 1
Exercise 2
Solution to Exercise 2
Case Studies
Designing a Hospital Management System
Designing a Hotel Management System
Designing a Bank Management System
Designing an Instagram
Designing an E-commerce Platform
Designing an Online Food Delivery System
Designing a Bank Management System

A Bank Management System (BMS) is a critical application used by banks to handle various services, such as managing customer accounts, processing transactions, managing loans, and overseeing employees. An effective database design ensures smooth operations, secure data handling, and efficient query execution.

In this case study, we’ll explore how to model a database for a Bank Management System (BMS) using Entity-Relationship (ER) diagrams and then map it to a relational database schema. By following this guide, you’ll gain the skills to design and implement a database for any banking system.

Requirements Analysis

Understanding the system's requirements is the first and most crucial step. It ensures that the database will support all necessary functionalities.

For our case study, let's outline the key functionalities that the HMS should support:

  • Customer Management: Storing and retrieving customer information.
  • Account Management: Maintaining account details (e.g., savings, current).
  • Transaction Processing: Handling deposits, withdrawals, and fund transfers.
  • Loan Management: Managing loan applications, approvals, and repayments.
  • Branch Management: Tracking bank branch details.
  • Employee Management: Maintaining employee roles and work details.
  • Card Management: Issuing and tracking credit/debit cards.
  • Account and Card Linking: Associating accounts with issued cards.

Step-by-Step ER Diagram Creation

We will build the ER diagram for the Hospital Management System through the following four steps.

Step 1: Identify Entities

Entities represent objects or concepts in the system that have data stored about them.

Primary Entities

  1. Customer
  2. Account
  3. Transaction
  4. Loan
  5. Loan_Repayment
  6. Branch
  7. Employee
  8. Card
Entities for Bank Management System
Entities for Bank Management System

Step 2: Detailing Attributes

We'll define attributes for each entity, including primary keys (PK) and foreign keys (FK).

1. Customer

  • Customer_ID (PK)
  • Full_Name
  • Address (Composite)
    • City
    • State
    • Area
  • Phone_Number
  • Email
  • Date_of_Birth
  • Identification_Type (e.g., Passport, ID Card)
  • Identification_Number

2. Account

  • Account_ID (PK)
  • Account_Type (Savings, Current)
  • Balance
  • Date_Opened
  • Branch_ID (FK)
  • Customer_ID (FK)

3. Transaction

  • Transaction_ID (PK)
  • Transaction_Type (Deposit, Withdrawal, Transfer)
  • Amount
  • Transaction_Date
  • Notes
  • Account_ID (FK)

4. Loan

  • Loan_ID (PK)
  • Loan_Type (Home, Personal, Car)
  • Loan_Amount
  • Interest_Rate
  • Start_Date
  • End_Date
  • Account_ID (FK)

5. Loan_Repayment

  • Repayment_ID (PK)
  • Repayment_Date
  • Amount
  • Loan_ID (FK)

6. Branch

  • Branch_ID (PK)
  • Branch_Name
  • Address
  • Phone_Number

7. Employee

  • Employee_ID (PK)
  • Name
  • Role
  • Email
  • Branch_ID (FK)

8. Card

  • Card_ID (PK)
  • Card_Type (Credit, Debit)
  • Expiry_Date
  • Card_Limit
  • Customer_ID (FK)
Attributes for Bank Management System
Attributes for Bank Management System

Step 3: Defining Relationships

Let’s define the relationships between entities in the system.

1. Customer and Account

  • A customer can have multiple accounts.
  • Each account belongs to one customer.
  • Type: One-to-Many (Customer to Account)

2. Account and Transaction

  • An account can have multiple transactions.
  • Each transaction is linked to one account.
  • Type: One-to-Many (Account to Transaction)

3. Account and Loan

  • An account can be associated with multiple loans.
  • Each loan is linked to one account.
  • Type: One-to-Many (Account to Loan)

4. Loan and Loan_Repayment

  • A loan can have multiple repayments.
  • Each repayment is linked to one loan.
  • Type: One-to-Many (Loan to Loan_Repayment)

5. Customer and Card

  • A customer can have multiple cards.
  • Each card belongs to one customer.
  • Type: One-to-Many (Customer to Card)

6. Account and Card

  • An account can be linked to multiple cards.
  • A card can be linked to multiple accounts.
  • Type: Many-to-Many (Account to Card)

7. Branch and Account

  • A branch manages multiple accounts.
  • Each account is associated with one branch.
  • Type: One-to-Many (Branch to Account)

8. Branch and Employee

  • A branch can have multiple employees.
  • Each employee works in one branch.
  • Type: One-to-Many (Branch to Employee)

Here is the final ER diagram.

ER Diagram For Bank Management System
ER Diagram For Bank Management System

Mapping the ER Diagram to a Relational Schema

When converting the ER diagram into a relational schema for our Bank Management System, we need to carefully handle various components to ensure data integrity and optimal performance. Here are the key considerations:

  • Entity Tables and Attributes:

    • Create a table for each entity identified in the ER diagram, such as Customer, Employee, Branch, etc.
    • Define all attributes for each table, specifying appropriate data types and constraints. Also, create seprate table for multi-valued attributes like Phone_Number.
    • Set primary keys (PK) for unique identification of records in each table.

  • Foreign Keys and Relationships:

    • Establish foreign keys (FK) to represent relationships between entities, linking tables through common attributes.
    • Ensure referential integrity by enforcing foreign key constraints, which maintain consistent and valid references between related tables.

  • Handling Many-to-Many Relationships:

    • Introduce associative (junction) tables to resolve many-to-many relationships into two one-to-many relationships.
      • For example, Card_Account connects Card and Account.
    • Include composite primary keys in associative tables, typically combining the primary keys of the related entities.

Here is the Relational Schema diagram.

Relational Schema Diagram for the Bank Management System
Relational Schema Diagram for the Bank Management System

Now, let's translate the ER model into SQL tables.

1. Customer Table

CREATE TABLE Customer (
    Customer_ID INT PRIMARY KEY,
    Full_Name VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    Area VARCHAR(50),
    Phone_Number VARCHAR(15),
    Email VARCHAR(50),
    Date_of_Birth DATE,
    Identification_Type VARCHAR(20), -- e.g., Passport, ID Card
    Identification_Number VARCHAR(50)
);

2. Account Table

CREATE TABLE Account (
    Account_ID INT PRIMARY KEY,
    Account_Type VARCHAR(20), -- e.g., Savings, Current
    Balance DECIMAL(10, 2),
    Date_Opened DATE,
    Branch_ID INT,
    Customer_ID INT,
    FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID),
    FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
);

3. Transaction Table

CREATE TABLE Transaction (
    Transaction_ID INT PRIMARY KEY,
    Transaction_Type VARCHAR(20), -- e.g., Deposit, Withdrawal
    Amount DECIMAL(10, 2),
    Transaction_Date DATE,
    Notes TEXT,
    Account_ID INT,
    FOREIGN KEY (Account_ID) REFERENCES Account(Account_ID)
);

4. Loan Table

CREATE TABLE Loan (
    Loan_ID INT PRIMARY KEY,
    Loan_Type VARCHAR(20), -- e.g., Home, Personal
    Loan_Amount DECIMAL(10, 2),
    Interest_Rate DECIMAL(5, 2),
    Start_Date DATE,
    End_Date DATE,
    Account_ID INT,
    FOREIGN KEY (Account_ID) REFERENCES Account(Account_ID)
);

5. Loan_Repayment Table

CREATE TABLE Loan_Repayment (
    Repayment_ID INT PRIMARY KEY,
    Repayment_Date DATE,
    Amount DECIMAL(10, 2),
    Loan_ID INT,
    FOREIGN KEY (Loan_ID) REFERENCES Loan(Loan_ID)
);

6. Branch Table

CREATE TABLE Branch (
    Branch_ID INT PRIMARY KEY,
    Branch_Name VARCHAR(50),
    Address VARCHAR(100),
    Phone_Number VARCHAR(15)
);

7. Branch_Phone_Number Table

CREATE TABLE Branch_Phone_Number (
    Branch_ID INT,
    Phone_Number VARCHAR(15),
    PRIMARY KEY (Branch_ID, Phone_Number),
    FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID)
);

8. Employee Table

CREATE TABLE Employee (
    Employee_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Role VARCHAR(50),
    Email VARCHAR(50),
    Branch_ID INT,
    FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID)
);

9. Card Table

CREATE TABLE Card (
    Card_ID INT PRIMARY KEY,
    Card_Type VARCHAR(20), -- e.g., Credit, Debit
    Expiry_Date DATE,
    Card_Limit DECIMAL(10, 2),
    Customer_ID INT,
    FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
);

10. Account_Card Table

CREATE TABLE Account_Card (
    Account_ID INT,
    Card_ID INT,
    PRIMARY KEY (Account_ID, Card_ID),
    FOREIGN KEY (Account_ID) REFERENCES Account(Account_ID),
    FOREIGN KEY (Card_ID) REFERENCES Card(Card_ID)
);
Designing a Hotel Management System
Designing an Instagram
Mark as Completed

On this page

Requirements Analysis

Step-by-Step ER Diagram Creation

Step 1: Identify Entities

Primary Entities

Step 2: Detailing Attributes

Step 3: Defining Relationships

Mapping the ER Diagram to a Relational Schema

  1. Customer Table
  1. Account Table
  1. Transaction Table
  1. Loan Table
  1. Loan_Repayment Table
  1. Branch Table
  1. Branch_Phone_Number Table
  1. Employee Table
  1. Card Table
  1. Account_Card Table